package com.api.gbhg.daizhishun.dao;

import cn.hutool.core.util.StrUtil;
import com.api.gbhg.daizhishun.util.ReadProperties;
import com.api.gbhg.liwenjun.VersionFlowUtil;
import com.api.gbhg.luoao.tool.ToolCURD;
import com.api.gbhg.luoao.utils.information;
import com.google.common.base.Joiner;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;

import java.io.UnsupportedEncodingException;
import java.util.*;

public class ReimbursementDao extends BaseBean {

    public static String sql;
    public static final Logger logger = LoggerFactory.getLogger("gbhg");


    /**
     * 查询主表数据
     * @param tablefname 表名
     * @param requestId 流程id
     * @param fieldname 经办人字段名
     * @return
     */
    /*String jbrname*/
    public static RecordSet getPrimaytable(String tablefname, String requestId,String fieldname,String money){
        StringBuffer sb = new StringBuffer();
        sb.append("select * from "+tablefname+" m,hrmResource e ,workflow_requestbase wr where " +
                " wr.requestid=m.requestid "+
                " and  m.requestId in("+requestId+") and m."+fieldname+" = e.id ");

       /* if (StrUtil.isNotEmpty(jbrname)){
            sb.append(" and  e.lastname like '%"+jbrname+"%'");
        }*/
       System.err.println(sb.toString()+"....."+money);
        RecordSet rs = new RecordSet();
        rs.executeQuery(sb.toString());
        return rs;
    }

    //获取明细表1
    public static RecordSet getDetail1(String tablefname,String mainid){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select * from "+tablefname+"_dt1 where mainid ="+mainid);
        return rs;
    }

    //获取明细表2
    public static RecordSet getDetail2(String tablefname,String mainid){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select * from "+tablefname+"_dt2 where mainid ="+mainid);
        return rs;
    }


    //查询部门经济支出分类
    public static String getbmjjczfl(String kdm){
        if (StrUtil.isNotEmpty(kdm)){
            RecordSet rs = new RecordSet();
            rs.executeQuery("select kmc  from  uf_jjzcflkmk where kdm ="+kdm);
            rs.next();
            return  rs.getStringNoTrim("kmc");
        }
        return  " ";

    }


    //查询项目名
    public static String getProjectname(String request){
        if (StrUtil.isNotEmpty(request)){
            RecordSet rs = new RecordSet();
            rs.executeQuery(" select xmmc from uf_jingfei where requestId = "+request+"  group by xmmc");
            rs.next();

            return   rs.getStringNoTrim("xmmc");
        }

        return  "";
    }


    //关联号
    public static String number(String ybzysxm){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select glh from  uf_xmglhgl where ybzysxm ="+ybzysxm);
        rs.next();
        return  rs.getStringNoTrim("glh");
    }



    //查询表名
    public static String tablefname(String requestId){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select  l.tablename,s.id  from workflow_requestbase r,workflow_base s,workflow_bill l where\n" +
                "r.workflowid = s.id and s.formid = l.id and  r.requestid ="+requestId);
        rs.next();
        return  rs.getStringNoTrim("tablename");
    }



    //根据requestid查询该流程数据
    public static  Map<String,Object> workflowinfo(String requestid,String requestname,String startMoney,String endMoney){

        ReadProperties properties = new ReadProperties();
        List<String> workflowid = properties.getworkflowid();

        //主表名
            String tablename = tablefname(requestid);

            //根据不同的表更改字段名
            String fieldname = "jbr";
            if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(1)))){
                fieldname = "jbry";
            }

            String money = "sqbxjehj";
            if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(3)))){
                money = "jlje";
            } else if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(2)))){
                money = "bxwbjehj";
            }else {
                money = "sqbxjehj";
            }

            String yslyname = "ysly";//预算来源
            String bmjjzcflname = "bmjjzcfl";//部门经济分类
            if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(4)))){
                bmjjzcflname = "bmysjjfl";
            }


            Map<String,Object> maps = new HashMap<>();
            //查询流程主表数据
            //jbrname
            RecordSet primaytable = ReimbursementDao.getPrimaytable(
                    tablename,requestid,fieldname,money);
            //查询主表数据
            while (primaytable.next()){
                double bxje = primaytable.getDouble(money);
                if(!startMoney.equals("") && !endMoney.equals("")){
                    double sm = Double.parseDouble(startMoney);
                    double em = Double.parseDouble(endMoney);
                    if(bxje>=sm && bxje<=em){
                        //System.err.println("符合金额查询："+bxje+">"+sm+">"+em);
                    }else{
                        //System.err.println("不符合金额查询："+bxje+">"+sm+">"+em);
                        continue;
                    }
                }else{
                    //System.err.println("没有金额查询："+bxje);
                }
                // 1000.00  sm 900 em 1000
                maps.put("requestid",primaytable.getStringNoTrim("requestid"));
                maps.put("requestname",primaytable.getStringNoTrim("requestname"));
                maps.put("jbr",primaytable.getStringNoTrim("lastname"));//经办人
                maps.put("sqrq",primaytable.getStringNoTrim("sqrq"));//申请日期
                maps.put("bxhjshzt",primaytable.getString("bxhjshzt"));//审核状态
                maps.put("sqbxjehj",primaytable.getStringNoTrim(money));//贷方金额
                maps.put("lcbh",primaytable.getStringNoTrim("lcbh"));//流程编号


                //YS10 缉私举报奖金报销
                if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(1)))){

                    List ysxm = new ArrayList();//预算项目
                    List bmjjzcfl = new ArrayList();//部门经济支出分类
                    List glh = new ArrayList();//关联号
                    Set ysly = new HashSet();//预算来源
                    String ly = primaytable.getStringNoTrim("ysly");
                    if (StrUtil.isNotEmpty(ly)){
                        ysly.add(information.getselectname(requestid,"ysly",ly));
                    }

                    String xm = primaytable.getStringNoTrim("ysxm");
                    if (StrUtil.isNotEmpty(xm)){
                        ysxm.add(ReimbursementDao.getProjectname(xm));
                        glh.add(ReimbursementDao.number(xm));
                    }

                    String fl=primaytable.getStringNoTrim(bmjjzcflname);
                    if (StrUtil.isNotEmpty(fl)){
                        bmjjzcfl.add(ReimbursementDao.getbmjjczfl(fl));
                    }
                    maps.put("ysly",  StringUtils.join(ysly.toArray(), ","));
                    maps.put("ysxm", Joiner.on(",").join((Iterable<?>) ysxm));
                    maps.put("bmjjzcfl",Joiner.on(",").join((Iterable<?>) bmjjzcfl));

                    List skr = new ArrayList();//收款人
                    skr.add(primaytable.getStringNoTrim("skr"));

                    maps.put("skr",Joiner.on(",").join((Iterable<?>) skr));
                }

                // YS06 培训费报销
                if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(0)))){

                    List ysxm = new ArrayList();//预算项目
                    List bmjjzcfl = new ArrayList();//部门经济支出分类
                    Set ysly = new HashSet();//预算来源
                    List glh = new ArrayList();//关联号
                    String ly = primaytable.getStringNoTrim("ysly");
                    if (StrUtil.isNotEmpty(ly)){
                        ysly.add(information.getselectname(requestid,"ysly",ly));
                    }

                    String xm = primaytable.getStringNoTrim("ysxm");
                    if (StrUtil.isNotEmpty(xm)){
                        ysxm.add(ReimbursementDao.getProjectname(xm));
                        glh.add(ReimbursementDao.number(xm));
                    }

                    String fl=primaytable.getStringNoTrim(bmjjzcflname);
                    if (StrUtil.isNotEmpty(fl)){
                        bmjjzcfl.add(ReimbursementDao.getbmjjczfl(fl));
                    }



                    maps.put("bmjjzcfl",Joiner.on(",").join((Iterable<?>) bmjjzcfl));
                    maps.put("ysly", StringUtils.join(ysly.toArray(), ","));
                    maps.put("ysxm", Joiner.on(",").join((Iterable<?>) ysxm));
                    maps.put("glh",Joiner.on(",").join((Iterable<?>) glh));

                }

                //主表id
                String mainid = primaytable.getStringNoTrim("id");

                //不是流程 YS10 缉私举报奖金报销  进行查询
                if (!tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(1))) && !tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(0)))){

                    //查询明细表1
                    RecordSet Detail1 = ReimbursementDao.getDetail1(tablename, mainid);

                    Set ysly = new HashSet();//预算来源
                    List ysxm = new ArrayList();//预算项目
                    List bmjjzcfl = new ArrayList();//部门经济支出分类
                    List glh = new ArrayList();//关联号
                    while (Detail1.next()){

                    String ly = Detail1.getStringNoTrim(yslyname);
                    if (StrUtil.isNotEmpty(ly)){
                        ysly.add(information.getselectname(requestid,yslyname,ly));
                    }

                    String xm = Detail1.getStringNoTrim("ysxm");
                    if (StrUtil.isNotEmpty(xm)){
                        ysxm.add(ReimbursementDao.getProjectname(xm));
                        glh.add(ReimbursementDao.number(xm));
                    }

                        String fl=Detail1.getStringNoTrim(bmjjzcflname);
                        if (StrUtil.isNotEmpty(fl)){
                            bmjjzcfl.add(ReimbursementDao.getbmjjczfl(fl));
                        }

                    }

                    maps.put("bmjjzcfl", Joiner.on(",").join((Iterable<?>) bmjjzcfl));
                    maps.put("ysly", StringUtils.join(ysly.toArray(), ","));
                    maps.put("ysxm", Joiner.on(",").join((Iterable<?>) ysxm));
                    maps.put("glh",Joiner.on(",").join((Iterable<?>) glh));
                }

                //不是流程 YS10 缉私举报奖金报销 进行查询
                if (!tablename.equals(VersionFlowUtil.getFlowTableName(workflowid.get(1))) ) {

                    //查询明细表2
                    RecordSet detail2 = ReimbursementDao.getDetail2(tablename, mainid);

                    List skr = new ArrayList();//收款人
                    while (detail2.next()){
                        skr.add(detail2.getStringNoTrim("skr"));
                    }
                    maps.put("skr",Joiner.on(",").join((Iterable<?>) skr));

                }
            }
        return maps;
    }



    //过滤报销流程
    public static RecordSet filter(String requestid,String startTime,String  endTime,String dw,String year,String requestId)  {

        RecordSet rs = new RecordSet();
        if (StrUtil.isNotEmpty(requestid)) {
            ReadProperties properties = new ReadProperties();
            String workflowid = properties.read2();//所有报销流程workflowid

            StringBuffer sb = new StringBuffer();
            sb.append("select se.requestid,ce.lastname,se.createdate,se.workflowid,se.requestname from  " +
                    "workflow_requestbase se,hrmResource ce " +
                    " where se.workflowid in(" + workflowid + ")  and se.creater = ce.id ");

            sb.append(" and se.requestid in(" + requestid + ")");
            sb.append(" and se.status in("+properties.Accountingnodename()+")");
            logger.info("会计待推送列表sql语句-------："+sb.toString());

            //条形码
            if (StrUtil.isNotEmpty(requestId)){
                sb.append(" and se.requestid = " + requestId);
            }

            //单位
            if (StrUtil.isNotEmpty(dw)) {
                sb.append(" and ce.subcompanyid1 =" + dw);
            }

            //年份
            if (StrUtil.isNotEmpty(year)) {
                sb.append(" and YEAR(se.createdate) =" + year);
            }

            //时间
            if (StrUtil.isNotEmpty(startTime)
                    && StrUtil.isNotEmpty(endTime)) {

                sb.append(" and se.createdate >='" + startTime +
                        "' and se.createdate<='" + endTime + "'");
            }

            sb.append(" order by se.requestid desc ");
            rs.executeQuery(sb.toString());
        }
        return rs;
    }



}
